package com.ruoyi.web;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.Date;
import java.util.List;

public class myimport2 {
    public static void main(String[] args) {
        String excelFilePath = "G:/ceshi.xlsx";
        String dbURL = "jdbc:mysql://localhost/ry";
        String user = "root";
        String password = "123456";

        try (Connection connection = DriverManager.getConnection(dbURL, user, password);
             FileInputStream inputStream = new FileInputStream(excelFilePath);
             Workbook workbook = new XSSFWorkbook(inputStream)) {

            Sheet sheet = workbook.getSheetAt(0); // 获取第一个Sheet

            String sql = "INSERT INTO my_test (id,test,name,sex,date,num) VALUES (?, ?,?,?,?,?)";
            PreparedStatement statement = connection.prepareStatement(sql);

            // 获取所有合并单元格的区域
            List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();

            int rowCount = 0;
            Integer id = 1; // 假设id是自增的，从1开始
            for (Row row : sheet) {
                if (rowCount == 0) { // 跳过标题行
                    rowCount++;
                    continue;
                }
                // 处理订单ID列（合并单元格）
                Cell orderIdCell = row.getCell(0);
                Integer orderId = getMergedCellValue(orderIdCell, mergedRegions, sheet);

                // 读取单元格数据（根据实际列顺序调整）
                String value1 = id.toString();
                String value2 = orderId.toString();
                String value3 = getCellValue(row.getCell(1));
                String value4 = getCellValue(row.getCell(2));
                Date value5 = getMergedCellDate(row.getCell(3), mergedRegions, sheet);
                String value6 = getMergedCellString(row.getCell(4), mergedRegions, sheet);



                id++;

                // 设置参数
                statement.setString(1, value1);
                statement.setString(2, value2);
                statement.setString(3, value3);
                statement.setString(4, value4);
                statement.setDate(5, new java.sql.Date(value5.getTime()));
                statement.setString(6, value6);


                statement.addBatch(); // 添加到批处理

                // 每100条执行一次批处理
                if (rowCount % 100 == 0) {
                    statement.executeBatch();
                }
                rowCount++;
            }

            statement.executeBatch(); // 执行剩余操作
            System.out.println("数据导入完成，共导入 " + (rowCount-1) + " 条记录");

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 处理不同类型单元格数据
    private static String getCellValue(Cell cell) {
        if (cell == null) return "";

        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    return String.valueOf((int) cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return "";
        }
    }

    private static int getMergedCellValue(Cell cell, List<CellRangeAddress> mergedRegions, Sheet sheet) {
        if (cell == null) {
            return -1; // 如果单元格为空，返回默认值
        }
        // 遍历所有合并区域
        for (CellRangeAddress region : mergedRegions) {
            // 检查当前单元格是否在合并区域内
            if (region.isInRange(cell.getRowIndex(), cell.getColumnIndex())) {
                // 获取合并区域的第一个单元格的值
                Row firstRow = sheet.getRow(region.getFirstRow());
                Cell firstCell = firstRow.getCell(region.getFirstColumn());
                return (int) firstCell.getNumericCellValue();
            }
        }

        // 如果不是合并单元格，直接返回当前单元格的值
        return (int) cell.getNumericCellValue();
    }

    private static Date getMergedCellDate(Cell cell, List<CellRangeAddress> mergedRegions, Sheet sheet) {
        if (cell == null) {
            return null; // 如果单元格为空，返回默认值
        }
        // 遍历所有合并区域
        for (CellRangeAddress region : mergedRegions) {
            // 检查当前单元格是否在合并区域内
            if (region.isInRange(cell.getRowIndex(), cell.getColumnIndex())) {
                // 获取合并区域的第一个单元格的值
                Row firstRow = sheet.getRow(region.getFirstRow());
                Cell firstCell = firstRow.getCell(region.getFirstColumn());
                return (Date) firstCell.getDateCellValue();
            }
        }

        // 如果不是合并单元格，直接返回当前单元格的值
        return (Date) cell.getDateCellValue();
    }

    private static String getMergedCellString(Cell cell, List<CellRangeAddress> mergedRegions, Sheet sheet) {
        if (cell == null) {
            return null; // 如果单元格为空，返回默认值
        }
        // 遍历所有合并区域
        for (CellRangeAddress region : mergedRegions) {
            // 检查当前单元格是否在合并区域内
            if (region.isInRange(cell.getRowIndex(), cell.getColumnIndex())) {
                // 获取合并区域的第一个单元格的值
                Row firstRow = sheet.getRow(region.getFirstRow());
                Cell firstCell = firstRow.getCell(region.getFirstColumn());
                return (String) firstCell.getStringCellValue();
            }
        }

        // 如果不是合并单元格，直接返回当前单元格的值
        return (String) cell.getStringCellValue();
    }
}
